﻿using HuiLian.Common.Attributes;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace HuiLian.Common.Helpers
{
    [SingleInstance]
    public class ExcelHelper
    {
        public string FilePath { get; set; } = string.Empty;
        public int SheetIndex { get; set; } = 0;
        public string FileExt { get { return Path.GetExtension(FilePath).ToLower(); } }

        public DataTable _dataTable;

        /// <summary>
        /// 初始化文件路径和Sheet序号
        /// </summary>
        /// <param name="filePath"></param>
        /// <param name="sheetIndex"></param>
        public ExcelHelper(string filePath = "", int sheetIndex = 0)
        {
            FilePath = filePath;
            SheetIndex = sheetIndex;
        }


        /// <summary>
        /// 转为指定类型
        /// </summary>
        /// <typeparam name="T"> </typeparam>
        /// <returns> </returns>
        public List<T> ToList<T>()
        {
            if (_dataTable is null) ToDataTable();

            var list = new List<T>();
            var type = typeof(T);
            var properties = type.GetProperties();

            foreach (DataRow row in _dataTable.Rows)
            {
                var t = Activator.CreateInstance<T>();
                foreach (var property in properties)
                {
                    if (!property.IsDefined(typeof(DescriptionAttribute), false))
                        continue;

                    Object[] attr = property.GetCustomAttributes(typeof(DescriptionAttribute), false);
                    if (attr.Length > 0)
                    {
                        DescriptionAttribute attribute = attr[0] as DescriptionAttribute;
                        var prop_name = attribute.Description;

                        //只有表格所包含的列才需要映射
                        if (_dataTable.Columns.Contains(prop_name))
                        {
                            var value = row[prop_name];
                            if (value != DBNull.Value)
                            {
                                var d = Convert.ChangeType(value, property.PropertyType);
                                property.SetValue(t, d);
                            }
                        }
                    }
                }
                list.Add(t);
            }
            return list;
        }


        /// <summary>
        /// Excel转为DataTable
        /// </summary>
        /// <returns> </returns>
        public ExcelHelper ToDataTable()
        {
            _dataTable = new DataTable();
            using (var fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook? workbook;

                if (FileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (FileExt == ".xls") { workbook = WorkbookFactory.Create(fs); } else { workbook = null; }

                if (workbook == null) { throw new Exception("Wrong File Type. Use .xlsx or .xls"); }

                var sheet = workbook.GetSheetAt(SheetIndex);
                if (sheet == null)
                {
                    throw new Exception("Excel sheet not found");
                }

                var headerRow = sheet.GetRow(0);
                var headerRowCount = headerRow.LastCellNum;
                for (var i = headerRow.FirstCellNum; i < headerRowCount; i++)
                {
                    var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    _dataTable.Columns.Add(column);
                }

                for (var i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    var dataRow = _dataTable.NewRow();
                    for (var j = row.FirstCellNum; j < headerRowCount; j++)
                    {
                        if(j == 8 || j == 9)
                        {
                            dataRow[j] = row.GetCell(j).DateCellValue;
                        }
                        else if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }

                    _dataTable.Rows.Add(dataRow);
                }


            }

            return this;
        }

        /// <summary>
        /// Excel转为DataTable
        /// </summary>
        /// <returns> </returns>
        public ExcelHelper ToDataTable_repairledget()
        {
            _dataTable = new DataTable();
            using (var fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook? workbook;

                if (FileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (FileExt == ".xls") { workbook = WorkbookFactory.Create(fs); } else { workbook = null; }

                if (workbook == null) { throw new Exception("Wrong File Type. Use .xlsx or .xls"); }

                var sheet = workbook.GetSheetAt(SheetIndex);
                if (sheet == null)
                {
                    throw new Exception("Excel sheet not found");
                }

                var headerRow = sheet.GetRow(0);
                var headerRowCount = headerRow.LastCellNum;
                for (var i = headerRow.FirstCellNum; i < headerRowCount; i++)
                {
                    var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    _dataTable.Columns.Add(column);
                }

                for (var i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    var dataRow = _dataTable.NewRow();
                    for (var j = row.FirstCellNum; j < headerRowCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                            if(j == 4)
                            {
                                dataRow[j] = row.GetCell(j).DateCellValue;
                            }
                        }
                    }

                    _dataTable.Rows.Add(dataRow);
                }


            }

            return this;
        }

        /// <summary>
        /// Excel转为DataTable
        /// </summary>
        /// <returns> </returns>
        public ExcelHelper ToDataTable_XieBan()
        {
            _dataTable = new DataTable();
            using (var fs = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
            {
                IWorkbook? workbook;

                if (FileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (FileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }

                if (workbook == null) { throw new Exception("Wrong File Type. Use .xlsx or .xls"); }

                var sheet = workbook.GetSheetAt(SheetIndex);
                if (sheet == null)
                {
                    throw new Exception("Excel sheet not found");
                }

                var headerRow = sheet.GetRow(2);
                var headerRowCount = headerRow.LastCellNum;
                for (var i = headerRow.FirstCellNum; i < headerRowCount; i++)
                {
                    var column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    _dataTable.Columns.Add(column);
                }

                for (var i = sheet.FirstRowNum + 1 + 2; i <= sheet.LastRowNum; i++)
                {
                    var row = sheet.GetRow(i);
                    var dataRow = _dataTable.NewRow();
                    for (var j = row.FirstCellNum; j < headerRowCount; j++)
                    {
                        int[] arrayIndex = new int[] { 3, 7, 9, 11 };
                        if(arrayIndex.Contains(j))
                        {
                            dataRow[j] = row.GetCell(j).DateCellValue;
                        }
                        else if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }

                    _dataTable.Rows.Add(dataRow);
                }


            }

            return this;
        }

        /// <summary>
        /// 获取时间戳
        /// </summary>
        /// <returns></returns>
        public static string GetTimeStamp()
        {
            //ToUniversalTime()将当前DateTime对象的值转换为世界标准时间(UTC)
            TimeSpan mTimeSpan = DateTime.Now.ToUniversalTime() - new DateTime(1970, 1, 1, 0, 0, 0);
            //得到精确到秒的时间戳（长度10位）
            long time = (long)mTimeSpan.TotalSeconds;
            return time.ToString();
        }
    }
}
